package sui.utils.excel;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSON;

/**
 * <p>
 * Description: 读取Excel生成SQL(MySQL)
 * </p>
 * @author XQF SUI
 * @created 2017年4月21日 上午10:22:00
 * @version 1.0
 */
public class ReaderExcelGenerateJSON {

	/**
	 * Excel文件路径
	 */
	private static final String EXCEL_FILE_PATH = "C:\\Users\\Administrator\\Desktop\\EC市场活动补录_20170424 - 副本.xlsx";
	
	/**
	 * Excel sheet页坐标
	 */
	private static final Integer EXCEL_SHEET_INDEX = 0;
	
	private static final Integer CUSTOMER_CODE_INDEX = 0;
	
	private static final Integer ACTIVITY_CODE_INDEX = 1;
	
	public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
		System.out.println("\n");
		System.out.println(readerExcelToJSON());
		System.out.println("\n");
	}
	
	/**
	 * <p>
	 * Desc: 读取Excel
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:16
	 * @return
	 * @throws EncryptedDocumentException
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	private static String readerExcelToJSON() throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		Sheet sheet = WorkbookFactory.create(new File(EXCEL_FILE_PATH)).getSheetAt(EXCEL_SHEET_INDEX);
		
		Map<String, Set<String>> map = new HashMap<>();
		
		for (Row row : sheet) {
			String CustomerCode = getStringCellValue(row.getCell(CUSTOMER_CODE_INDEX));
			String actCode = getStringCellValue(row.getCell(ACTIVITY_CODE_INDEX));
			
			if (map.containsKey(actCode)) {
				map.get(actCode).add(CustomerCode);
			} else {
				Set<String> customerSet = new HashSet<String>();
				customerSet.add(CustomerCode);
				map.put(actCode, customerSet);
			}
		}
		
		return JSON.toJSONString(map);
	}
	
	/**
	 * <p>
	 * Desc: 获取String类型的Cell值
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:06:38
	 * @param cell
	 * @return
	 */
	private static String getStringCellValue(Cell cell) {
		String strCell = null;
		
		switch (cell.getCellTypeEnum()) {
		case STRING:
			strCell = cell.getStringCellValue();
			break;
		case NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case BLANK:
			break;
		default:
			break;
		}
		
		return strCell;
	}
	
}
